#!/usr/bin/env python
import re
import xml.parsers.expat
import sys
import cherrypy as cp
#import formalchemy
from formalchemy import Field, FieldSet, Grid, types, FieldRenderer,CheckBoxSet,validators
from formalchemy.fields import SelectFieldRenderer
import hashlib
from sqlalchemy.sql.expression import *
from sqlalchemy.orm import join
from database import *
from templates import serve_template as render


session = None
root_session = None

#----------------------------------------------------------------------------#
#------------------XML--Ficha-del-Cliente------------------------------------#
#----------------------------------------------------------------------------#

class ServeXMLClientData(object):

    """Here we will give the information about the clients in xml format. """
    @cp.expose
    def index(self, cid=None, numero=None, ctaid=None):
        cp.response.headers['content-type'] = 'text/xml'    
        clients = None
    
        if numero:
            line = root_session.query(Line).filter(Line.number == numero).one()
            contract = root_session.query(Contract).filter(Contract.lines.contains(line)).one()
            account = root_session.query(Account).filter(Account.contracts.contains(contract)).one()
            clients = root_session.query(Client).filter(Client.accounts.contains(account)).all()
        elif ctaid:
            account = root_session.query(Account).filter(Account.id == ctaid).first()
            return render('account-xml.mako', account=account)
        elif cid:
            clients = root_session.query(Client).filter(Client.id == cid).all()
        else:
            clients = root_session.query(Client).all()
        return render('client-xml.mako',clients=clients)

#----------------------------------------------------------------------------#
#------------------Atencion-Al-Cliente---------------------------------------#
#----------------------------------------------------------------------------#
        
class ServeClientPage(object):
    """This is the page dedicated to client atention."""

    @cp.expose
    def product_list(self,number=0, tags="", brand_id=""):
        """Displas the product list"""

        ir = InventoryRequest(root_session)
        products = ir.getProductCatalog(tags=tags,brand_id = brand_id)
        if products is None:
            return "Error al comunicarse con inventario. lista de prod"
        if len(products) == 0:
            return """No existen productos que cumplan con su criterio de busqueda, 
                    si usted no uso filtros para su busqueda, no hay productos en inventario."""
        return render('product-list.mako', products=products,number=number)
        
    
    @cp.expose
    def index(self):        
        
        page = Page('Atencion al Cliente','/serve_client')
        page.checkpermissions()
        return render("client-serve.mako",page=page)


    @cp.expose
    def add_company(self,**kwargs):
        """This proc inserts in the database a company, it generates
         python code to insert the company, and 0 or many contactacts."""
        objects_to_create = []
        instructions_to_exec = []
        contact_list = []
        
        for k,v in kwargs.iteritems():
            #los argumentos son de la forma Tabla--campo=valor, los de los contactos son X-Tabla--campo=valor donde X es el numero de contacto
            if k.count("-") == 3:   #pa las forms que se repiten
                arg_values=k.split("--")
                numbertable = arg_values[0].split('-')
                object_cons = numbertable[1]
                object_name = numbertable[1].lower() + numbertable[0]
                object_attr = arg_values[1]
                object_value = v
                if object_cons == "Contact" and object_name not in contact_list:
                    contact_list.append(object_name)
                if (object_cons,object_name) not in objects_to_create:
                    objects_to_create.append((object_cons,object_name))
                instructions_to_exec.append("%s.%s = '%s'" % (object_name,object_attr,object_value))
        
            elif k.count("-") == 2: #pa las forms que no se repiten Tabla--campo=valor
        
                arg_values=k.split("--")
                object_cons = arg_values[0]
                object_name = arg_values[0].lower()
                object_attr = arg_values[1]
                object_value = v                
                if (object_cons,object_name) not in objects_to_create:
                    objects_to_create.append((object_cons,object_name))
                instructions_to_exec.append("%s.%s = '%s'" % (object_name,object_attr,object_value))
            
        for object_cons, object_name in objects_to_create:
            print "###Exec### %s = %s()" % (object_name, object_cons)
            exec("%s = %s()" % (object_name, object_cons))

        for inst in instructions_to_exec:
            print "###Exec### %s" % inst
            exec(inst)
            
        s = "company.contacts = ["
        for contact in contact_list:
            s+=contact+","
        s = s[0:-1] + "]"
        if len(contact_list)>0:
            print "###Exec### "+s
            exec(s)
        
        for object_cons, object_name in objects_to_create:
            print "###Exec### session.add("+str(object_name)+")"
            exec("session.add("+str(object_name)+")")
        company.birthdate = "%s-%s-%s" % (company.birthdate__year,company.birthdate__month,company.birthdate__day)
        try:
            session.commit()        
        except Exception,e:
            session.rollback()
            #session.commit()
            return "%s" % e.args
            
        
        #redireccion a atender al cliente, dond se mostrara la info de este cliente como que si lo hubieramos buscado en la DB
        raise cp.HTTPRedirect("/serve_client/main?client_id=%s&readonly=True" % company.id)

    @cp.expose
    def main(self,client_id=None,readonly=False):
        page = Page("Pagina Principal de Atencion al Cliente","/serve_client/main")
        page.checkpermissions()    

        
        ir = InvoiceRequest(root_session)
        ir.sincwithinvoicemodule(root_session)

       
        if client_id is None:
            return render('error.mako',page=page,error="Error: Link invalido.")
             
        client = session.query(Client).filter(Client.id == client_id).first()
        company = session.query(Company).filter(Company.id == client_id).first()

        customer = None
        if company:
            customer = company
            fs = FieldSet(company)
            
            fs.configure(
                readonly=True,
                include=[
                    fs.name.label("Nombre"),
                    fs.address.label("Direccion"),
                    fs.telephone.label("Telefono"),
                    fs.email,
                    fs.birthdate.label("Fundada En"),
                    fs.web_site,
                    fs.fax,
                    fs.legal_repr_name.label("Nombre Representante Legal"),
                    fs.personal_id.label("Cedula Representante Legal"),
                    fs.legal_repr_telephone.label("Telefono Representante Legal"),
                    fs.legal_repr_email.label("Email Representante Legal"),
            ])    
            
        elif client:
            customer = client
            fs = FieldSet(client)
            fs.configure(            
                readonly=True,
                include=[
                    fs.name
                ])
        else:
            return render('error.mako',page=page,msg="Error: Ese cliente no existe.")
        
        incidence_req = IncidenceRequest(root_session)
        incidence_history = incidence_req.getHistory(client_id = customer.id)
        inventory_req = InventoryRequest(root_session)

        return render('client-main.mako',page=page,fs=fs,client=customer, incidence_history = incidence_history, inventory_req=inventory_req)
        
    @cp.expose
    def search(self):
        return render('client-search-filter.mako')
    
    @cp.expose
    def client_list(self, name=None, personal_id=None, telephone=None):
        page = Page("Buscar Cliente","/serve_client/search")
        if not haspermissions(page.url):
            return "No esta permitido realizar esta accion."
        clients = session.query(Client)

        if name and len(name) > 0:
            clients = clients.filter(Client.name == name)
        if personal_id and len(personal_id) > 0:    
            clients = clients.filter(Client.personal_id == personal_id)
        if telephone and len(telephone) > 0:    
            clients = clients.filter(Client.telephone == telephone)

        if clients.first() is None:
            return "La busqueda no produjo ningun resultado."
        grid = Grid(Client,clients,session=session)
        
        grid.add(Field(name="client_data", \
            value=lambda \
            item: '<a  href="/serve_client/main?client_id=%s&readonly=True">%s</a>'\
            % (item.id, 'Seleccionar')))
        
        
        grid.configure(readonly=True,include=[
            grid.name.label("Nombre"),
            grid.personal_id.label("Cedula"),
            grid.telephone.label("Telefono"),
            grid.client_data
        ])
        return render('client-search.mako',page=page,grid=grid)

        
    @cp.expose
    def search_client(self):
        page = Page('Buscar Cliente','/serve_client/search_client')
        page.checkpermissions()
        return render("client-search.mako",page=page)
        
#----------------------------------------------------------------------------#
#-----------------------Paginas-Mantenimiento-General------------------------#
#----------------------------------------------------------------------------#

class TablesAdminPage(object):

    @cp.expose
    def index(self):
        page = Page("Pagina de Administracion de tablas",'/tables_admin')
        return render('admin.mako', page=page)  

    @cp.expose
    def account_main(self, submit=None, client=None, **kwargs): 
            page=Page("Cuentas",'/tables_admin/account_main')
            checkpermissions(page.url)
            if submit is None:
                
                return render('account-main.mako', page=page)
            else:
                if client is None:
                        return render('error.mako', page=page, error='Favor Ingresar el nombre de un cliente')  
                try:
                    client_id=session.query(Client).filter(Client.name==client).first().id       
                except Exception as e:
                       return render('error.mako', page=page, error='Cliente no encontrado')		
                raise cp.HTTPRedirect('/tables_admin/new/account_data/?client_id=%s'%client_id)

    @cp.expose
    def category(self,submit=None,**kwargs):
        page = Page('Categoria del Cliente','/tables_admin/category')
        checkpermissions(page.url)
        categories = session.query(ClientCategoryCatalog).all()
        cat_config = session.query(ClientCategoryConfiguration).filter(ClientCategoryConfiguration.id==1).first()
        if submit is None:
            grid = Grid(ClientCategoryCatalog, categories, session=session)
            grid.configure(include=[
                grid.name.label("Categoria"),
                grid.rangea.label("Cantidad Inicial"),
                grid.rangeb.label("Cantidad Final"),
            ])
            if cat_config is None:
                fs = FieldSet(ClientCategoryConfiguration, session=session,data=kwargs)
            else:
                fs = FieldSet(cat_config, session=session,data=kwargs)
            fs.configure(include=[
                fs.money.label("Ingresos:"),
                fs.since.label("Desde:"),
            ])

            return render('category.mako',page=page,grid=grid,fs=fs)
        else:
            grid = Grid(ClientCategoryCatalog,categories, session=session, data=kwargs)
            grid.configure(include=[
                grid.name.label("Categoria"),
                grid.rangea.label("Cantidad Inicial"),
                grid.rangeb.label("Cantidad Final")])  
            if cat_config is None:
                fs = FieldSet(ClientCategoryConfiguration, session=session,data=kwargs)
            else:
                fs = FieldSet(cat_config, session=session,data=kwargs)
            fs.configure(include=[
                fs.money.label("Ingresos:"),
                fs.since.label("Desde:"),
            ])
            if grid.validate():
                try:
                    grid.sync()
                    fs.sync()
                    session.commit()
                except Exception as e:
                    session.rollback()
                    return render('error.mako',page=page,error="Inesperado %s" % e.args)
            raise cp.HTTPRedirect('/tables_admin/category')

        
    @cp.expose
    def client_data(self,submit=None):
    

        page = Page("Listado de Clientes","/tables_admin/client_data")
        checkpermissions(page.url)

        
        c = session.query(Client).all()
        grid = Grid(Client, c, session=session)
        
        
        grid.add(Field(name="edit", \
        value=lambda \
        item: '<a  href="/tables_admin/new/client_edit/?client_id=%s">%s</a>'\
        % (item.id, 'editar')))
        
        grid.add(Field(name="cuenta", \
        value=lambda \
        item: '<a  href="/tables_admin/new/account_data/?client_id=%s">%s</a>'\
        % (item.id, 'ver cuentas')))


        
        grid.configure(include=[
            grid.name.label("Nombre").readonly(),
            grid.edit.label("Editar").readonly(),
            grid.cuenta.readonly()
            
        ])
        return render('admin-list.mako',page=page, grid=grid)


        
#----------------------------------------------------------------------------#
#----------------------------------------------------------------------------#
#----------------------------------------------------------------------------#

def formatIncludedServices(services):
    r = "<table border = 1><tr><td>Nombre</td><td>Precio</td></tr>"
    for service in services:
        if not service.is_required:     
            r = r + "<tr><td>"+service.name + '</td><td>Q.'+service.price+'</td></tr>'
    return r + "</table>"

def formatServices(plan_id,services,number):
    r = "<table border = 1><tr><td>Nombre</td><td>Precio</td></tr>"
    for service in services:
        if service.is_required:     
            r = r + """
                <tr><td><input type="checkbox" value="%s" name="%s-Plan-%s-optional_service_id"/>
                %s </td><td>Q. %s </td><tr>
                """ % (service.id, number, plan_id ,service.name, service.price)

    return r + "</table>"   
    
#----------------------------------------------------------------------------#
#--------------------Add-Edit-mantenimiento----------------------------------#
#----------------------------------------------------------------------------#

class TableObjectAddEdit(object):

    @cp.expose
    def add_category(self,submit=None,**kwargs):
        page = Page('Nueva Categoria','/tables_admin/new/add_category')
        checkpermissions(page.url)
        if submit is None:
            fs = FieldSet(ClientCategoryCatalog, session=session)
            fs.configure(include=[
                fs.name.label("Nombre"),
                fs.rangea.label("Cantidad Inicial"),
                fs.rangeb.label("Cantidad final")
                
            ])
            return render('new-category.mako',page=page,fs=fs)
        else:
            fs = FieldSet(ClientCategoryCatalog, session=session,data=kwargs)
            fs.configure(include=[
                fs.name,
                fs.rangea,
                fs.rangeb
            ])
            fs.sync()
            session.commit()
            raise cp.HTTPRedirect('/tables_admin/category')

    @cp.expose
    def line(self, number=0):
        #if not haspermissions('/tables_admin/new/line'):
        #    return "Accion invalida. No tiene permisos."

        ir = InventoryRequest(root_session)

#request Data
        line_numbers = ir.getNumberList(number_quantity = 1)
        search_tags = ir.getTags()
#check for errors
        if line_numbers is None or search_tags is None:
            return "<h2>Error al comunicarse con Inventario. Agregar Linea</h2>"
            
        if len(line_numbers) == 0:
            return "<h2>No hay numeros disponibles en inventario.</h2>"
        
        if len(search_tags) == 0:
            return "<h2>No se lograron obtener los filtros de busqueda de Inventario.</h2>"
        #
        plan_grid = Grid(Plan,session.query(Plan).all(), session=session, prefix = number)
        
        plan_grid.add(Field(
            name="choose_plan",
            value=lambda item: '<input type="radio" name="Line-%s-plan_id" value="%s" /> %s' % (number, item.id, item.name)))
        
        plan_grid.add(Field(
            name="extra_services",
            value=lambda item: '%s' % formatServices(item.id,item.services,number)))

        plan_grid.add(Field(
            name="included_services",
            value=lambda item: '%s' % formatIncludedServices(item.services)))


        plan_grid.configure(
            include = [
                plan_grid.choose_plan.label("Nombre").readonly(),
                plan_grid.description.label('Descripccion').readonly(),
                plan_grid.included_services.label('Servicios Incluidos').readonly(),
                plan_grid.extra_services.label('Servicios Opcionales').readonly()
            ]
        )
        
        ir = InventoryRequest(root_session)
        brand_catalog = ir.getBrandCatalog()
        brand_list = []
        for brand in brand_catalog:
            brand_list.append((brand.name,brand.name))

        brand_field = Field(name="test").dropdown(brand_list)

            
        return render('line-add.mako',
            number=number,
            line_numbers = line_numbers,
            plan_grid = plan_grid,
            search_tags = search_tags,
            brand_catalog = brand_catalog,
            brand_field=brand_field)
        
    @cp.expose
    def contract(self,account_id=None,submit = None, return_url = None, rend=False, how_many_lines = 5, **kwargs):
        
        ir = InvoiceRequest(root_session)
        ir.sincwithinvoicemodule(root_session)

        ir = InventoryRequest(root_session)
    
        page=Page("Nuevo Contrato", '/tables_admin/new/contract')
        checkpermissions(page.url)
        if account_id == None:
            return render('error.mako',error='Link Invalido',page=page)
            
        account = session.query(Account).filter(Account.id == account_id).first()
        page.title = page.title+" para la cuenta de "+account.name
       # cuenta=session.query(Contract).filter(Contract.account_id == account_id).all()
        if submit is None:
            #pedir a inventario 10 numeros con getnumberlist tonces digamos 
            #line_numbers = getNumberList(how_many_lines) = ['234','345','345','4','5']
            fs = FieldSet(Contract,session=session)
            fs.configure(include=[
                fs.start_date.label("Fecha de inicio"),
                fs.end_date.label("Fecha de finalizacion")])        
            #return plan_grid.render()
            if rend==False:
                return render('contract-add.mako',page=page,fs=fs,account_id=account_id,agent_id = cp.session.get('logged_as').id)
            else:
                return render('contract-add2.mako',page=page,fs=fs,account_id=account_id,agent_id = cp.session.get('logged_as').id)

        else:
         
        #verificar que se reciben los args minimos requeridos
            minimal_required_args = [
            #who created the contract to what account
            'Contract--account_id',
            'Contract--agent_id',
            #start and exp date
            'Contract--start_date__month',
            'Contract--start_date__year',
            'Contract--start_date__day',

            'Contract--end_date__month',
            'Contract--end_date__year',
            'Contract--end_date__day',
            #Number,producto,plan that will be related with the line
            
            'Line-0-number',
            'Line-0-product_id',
            'Line-0-plan_id']
            
            for arg in minimal_required_args:
                if kwargs.get(arg) is None:
                    return render('error.mako',page=page, error="Asegurese de seleccionar las fechas, y por cada linea un producto y un plan como minimo.")

            if kwargs['Contract--start_date__day'] == "DD" and kwargs['Contract--start_date__month'] == "MM" and kwargs['Contract--start_date__year'] == "YYYY" :
               return render('error.mako',page=page, error="Debe de especificar una fecha.")
                    
            objects_to_create = []
            instructions = []
            contract_lines = []
            
            contract = Contract()
            contract.agent_id = cp.session.get('logged_as').id
            contract.account_id = account_id

            contract.start_date = "%s-%s-%s" % (
                kwargs['Contract--start_date__day'],
                kwargs['Contract--start_date__month'],
                kwargs['Contract--start_date__year'])
            
            
                #TODO: verificar que las n lineas manden toda su info

            #averiguar cuantas lineas se van a agregar en base a los args
            line_count = 0
            for k,v in kwargs.iteritems():
                sarg = k.split('-')
                if len(sarg) == 3 and sarg[0] == "Line" and sarg[2] == "number":
                    line_number = int(sarg[1])
                    if line_number > line_count:
                        line_count = line_number
            line_count = line_count + 1 #porque contamos desd la linea 0 y queremos saber cuantas lineas se quieren agregar

          
            try:
                
                for line_number in range(line_count):
                    if not (kwargs.get('Line-%s-number' % line_number) and kwargs.get('Line-%s-product_id' % line_number) and kwargs.get('Line-%s-plan_id' % line_number)):
                        session.rollback()
                        return render('error.mako',page=page, error='No se pudo crear el contrato porque no ingreso los datos necesarios, por favor asegurece de haber ingresado para cada linea, que producto se llevara el cliente bajo que plan.')
                    exec("line%s = Line()" % line_number)
    
                    #descargar linea.
                    #------------Inventory-TakeNumber------------------------------#
                    trans = ir.transaction(
                        url = "/services/numeros/take",
                        url_args="?id="+kwargs['Line-%s-number'% line_number])
                    if trans.get('status').lower() == "error":
                        session.rollback()
                        return render('error.mako',page=page, error= """
                            La transaccion numero %s con inventario ha sido abortada.<br>
                            Error al descargar linea.<br>
                            Mensaje: %s<br>
                            """ % (trans.get('attrs').get('id'),trans.get('mensaje')))
                    #-----------------------------------------------------------------#        
                    
                    #agregar linea al client.
                    exec("line%s.number = kwargs['Line-%s-number']" % (line_number, line_number))
                    
                    
                    #descargar producto 
                    #------------ Inventory TakeProduct ------------------------------#
                    trans = ir.transaction(url = '/services/terminales/take?ids=('+  kwargs["Line-%s-number" % line_number]  +',1)')

                    if trans.get('status').lower() == "error":
                        session.rollback()
                        return render('error.mako',page=page,error= """
                            La transaccion numero %s con inventario ha sido abortada.<br>
                            Error al descargar el producto.<br>
                            Mensaje: %s<br>
                            """ % (trans.get('attrs').get('id'),trans.get('mensaje')))
                    
                    #-----------------------------------------------------------------#                    
                    #agregar que producto fue comprado con la linea
                    exec("line%s.product_id = kwargs['Line-%s-product_id']" % (line_number,line_number))
                    chosen_plan = session.query(Plan).filter(Plan.id == kwargs['Line-%s-plan_id' % line_number]).first()
                    exec("line%s.plans  = [chosen_plan]" % (line_number))

                    #------------------------------------------------------------------#
                    #agregar al contrato que servicios opcionales se llevo con el plan
                    #formato del argumento (number)-Plam-(plan_id)-optional_service_id=service_id
                    for service in chosen_plan.services:
                        if service.is_required:
                            #agregar ala linea
                            exec("line%s.services.append(service)" % line_number)
                        else:
                            #revisar si fue escogido
                            if kwargs.get("%s-Plan-%s-optional_service_id" % (line_number,chosen_plan.id)):
                                #agregar a la linea
                                exec("line%s.services.append(service)" % line_number)
                                
                            
                    
                    exec("contract.lines.append(line%s)" % line_number)
                    exec("session.add(line%s)" % line_number)

                print "##exec##: session.add(contract)"
                session.add(contract)

                print "##exec##: session.commit()"            
                session.commit()
            
            except Exception , e:
                print "##exec##: session.rollback()"
                session.rollback()
                return render('error.mako',page=page, error = "Inesperado, Transaccion Abortada descripccion del error: %s" % e.args)

            #------------------Invoice Notify Changes--------------------------#
            
            invoice_r = InvoiceRequest(session)
            invoice_r.notifyChanges(session=session,action=InvoiceRequest.UP, account_id=account_id)
            
            #-----------------------------------------------------------------#
            #"Transaccion completa"
            raise cp.HTTPRedirect('/serve_client/main?client_id=%s&readonly=True' % account.client_id)
    
    @cp.expose
    def company(self, submit=None, company_id=None, basichtml=None, **kwargs):

        page = Page('Nuevo Cliente','/tables_admin/new/company')
        page.checkpermissions()
        if submit: #data manipulation
            if submit == "Editar": #edit
                pk = parseArg(kwargs.keys()[0])[1]
                company = session.query(Company).filter(Company.id == pk).first()
                if company is None:
                    return render('error.mako',page=page,
                        error="""Esa Empresa no existe. """)
                                
                fs = FieldSet(company,session=session,data=kwargs)
                


            fs.configure(include=[
                fs.name,
                fs.address,
                fs.telephone,
                fs.email,
                fs.birthdate,
                fs.web_site,
                fs.fax
            ])

            try:
                session.commit()               
            except Exception , e:
                session.rollback()
                return render('error.mako',
                    page=page, 
                    error='Error: %s' % e.args,
                    basichtml=basichtml)

        else: #form generation
            if company_id is None: #new 
                company_fs = FieldSet(Company,session = session)
                legal_repr_fs = FieldSet(Contact, session=session)
                contact_fs = FieldSet(Contact, session=session)
                field_sets = {'company':company_fs,'legal_repr':legal_repr_fs,'contact':contact_fs}
                
                company_fs.configure(include=[
                    company_fs.name.label("Nombre"),
                    company_fs.address.label("Direccion"),
                    company_fs.telephone.label("Telefono"),
                    company_fs.email,
                    company_fs.web_site,
                    company_fs.fax,
                    company_fs.classification.label("Classificacion"),
                    company_fs.birthdate.label("Fundada En"),
                    company_fs.legal_repr_name.label("Nombre Representante Legal"),
                    company_fs.personal_id.label("Cedula Representante Legal"),
                    company_fs.legal_repr_telephone.label("Telefono Representante Legal"),
                    company_fs.legal_repr_email.label("Email Representante Legal"),
                ])

                page.title = 'Agregar Cliente'
                return render('company-add-edit.mako',
                    page=page,
                    action='',
                    submit_value = "Crear",
                    field_sets = field_sets,
                    basichtml=basichtml)
            else: #edit 
                company = session.query(Company).filter(Company.id==company_id).first()
                if company is None:
                    return render('error.mako', page=page,error='Esta empresa no existe.',basichtml=basichtml)
                    
                fs = FieldSet(company)

                fs.configure(include=[
                    fs.name,
                    fs.address,
                    fs.telephone,
                    fs.email,
                    fs.birthdate,
                    fs.web_site,
                    fs.fax
                ])
                page.title = 'Editar Accion'
                return render('admin-add-edit.mako',
                    page=page,
                    fs=fs,
                    action='',
                    submit_value = "Editar",
                    basichtml=basichtml)
        

 
 #WAAAAAAAAAAAAAAA\/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	
#WAAAAAAAAAAAAAAAAKIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    def set_category(self,client_id=None):
    
        invoice_request = InvoiceRequest(root_session)
        accounts = session.query(Account.id).filter(client_id==Account.client_id).all()
        amount=0
        mora=0
        date = session.query(ClientCategoryConfiguration.since).filter(ClientCategoryConfiguration.id==1).one()
        dinero = session.query(ClientCategoryConfiguration.money).filter(ClientCategoryConfiguration.id==1).one()
        client = session.query(Client).filter(Client.id==client_id).one()
        for i in range(len(accounts)):
            lista = invoice_request.getInvoiceHistory(account_id=accounts[i],date=date)
            for j in range(len(lista)):
                if dinero == True:
                    amount+=lista[j].amount
                else:
                    if lista[j].paid== False:
                        mora+=1
                        
        category = session.query(ClientCategoryCatalog).all()
        for l in range(len(category)):
            if ((amount > category.rangea) and (amount < category.rangeb)):
                client.category=category.name
                break
        if client.category is None:
            client.category='N/A'
            
    @cp.expose
    def contact(self,number=0):
        page = Page('Nuevo Contacto','/tables_admin/new/contact')
        #checkpermissions(page.url)
        fs = FieldSet(Contact, session=session, prefix=number)
        fs.configure(include=[
            fs.name.label("Nombre"),
            fs.email,
            fs.telephone.label("Telefono"),
            fs.description.label('Descripccion')
        ])
        return "<h3>Contacto "+str(int(number)+1)+"</h3>"+fs.render()

    


    @cp.expose  
    def contract_data(self,account_id=None,rend=False, submit=None):
        page = Page("Listado de Contratos","/tables_admin/new/contract_data")
        checkpermissions(page.url)
        account = session.query(Account).filter(Account.id == account_id).first()
        contracts = session.query(Contract).filter(Contract.account_id == account_id ).all()
        grid = Grid(Contract, contracts, session=session)
        grid.add(Field(name="agent", \
        value=lambda \
        item: "%s" % session.query(User.name).filter(User.id== item.agent_id).first()))
        
        grid.add(Field(name="edit", \
        value=lambda \
        item: '<a  href="/tables_admin/new/contract_edit/?account_id=%s&contract_id=%s">%s</a>'\
        % (account_id,item.id, 'Editar')))
        grid.configure(include=[
            grid.start_date.label("Fecha de Inicio").readonly(),
            grid.end_date.label("Fecha de Finalizacion").readonly(),
            grid.agent.label("Creado por").readonly(),
            #user = cp.session.get('logged_as')
            grid.edit.label("Editar").readonly()
            ])
        if rend==False:
            return render('contract-list.mako',page=page, grid=grid, cuenta=account.name,account_id=account_id)
        else:
            return render('contract-list2.mako',page=page, grid=grid, cuenta=account.name,account_id=account_id)
    
    
            
    @cp.expose  
    def account_data(self,client_id=None, account_id=None,rend=False, submit=None):

        page = Page("Listado de Cuentas","/tables_admin/new/account_data")
        checkpermissions(page.url)
        client = session.query(Client).filter(Client.id == client_id).first()
        accounts = session.query(Account).filter(Account.client_id == client_id).all()
        grid = Grid(Account, accounts , session=session)
        grid.add(Field(name="edit", \
        value=lambda \
        item: '<a  href="/tables_admin/new/account_edit/?account_id=%s&client_id=%s">%s</a>'\
        % (item.id,client_id, 'editar')))
        grid.add(Field(name="contract", \
        value=lambda \
        item: '<a  href="/tables_admin/new/contract_data/?account_id=%s">%s</a>'\
        % (item.id, 'ver contrato')))
        grid.configure(include=[
            grid.name.label("Nombre").readonly(),
            grid.nit.readonly(),
            grid.address.label("Direccion").readonly(),
            grid.edit.label("Editar").readonly(),
            grid.contract.label("Agregar contrato").readonly()
        ])
        if rend==False:
            return render('account-list.mako',page=page, grid=grid,return_url = None, chato=client.name,client_id=client_id)
        else:
            return render('account-list2.mako',page=page, grid=grid,return_url = None, chato=client.name,client_id=client_id)

    @cp.expose
    def client_data(self,submit = None,rend=False,return_url=None, **kwargs):
        page=Page("Nueva Ficha del Cliente", '/tables_admin/new/client_data')
        checkpermissions(page.url)
        
        if submit is None:
            fs = FieldSet(Client,session=session)
            fs.configure(include=[fs.name.label("Nombre"),
                fs.address.label("Direccion"),
                fs.telephone.label("Telefono"),
                fs.email,fs.personal_id.label("Cedula"),
                fs.classification.label("Clasificacion"),
                fs.birthdate.label("Fecha de Nacimiento")])
            if rend==False:
                return render('client-add.mako',page=page,fs=fs)
            else:
                return render('client-add2.mako',page=page,fs=fs)

        else:
            fs = FieldSet(Client,session=session,data=kwargs)
            fs.configure(include=[fs.name,
                fs.address,
                fs.telephone,
                fs.email,
                fs.personal_id,
                fs.classification,
                fs.birthdate])
            fs.sync()
            session.commit()
            clients = root_session.query(Client).all()
            last_id = 0
            for c in clients:
                if c.id > last_id:
                    last_id = c.id
            
            if return_url is None:
                raise cp.HTTPRedirect('/tables_admin/client_data')
            else:
                raise cp.HTTPRedirect('/serve_client/main?client_id=%s&readonly=True'%last_id)
        
            
    @cp.expose
    def account(self,client_id= None, return_url= None, rend=False, submit = None, **kwargs):
        page=Page("Nueva Cuenta", '/tables_admin/new/account')
        checkpermissions(page.url)
       # cuenta=session.query(Account).filter(Account.client_id == client_id).all()
        if submit is None:
            
            fs = FieldSet(Account,session=session)
           # fs.add(Field('billing'))
            fs.configure(include=[fs.name.label("Nombre"),
                                  fs.address.label("Direccion"),
                                  fs.nit,
                                  fs.billing.dropdown(options=[
                                                                ('1', 1),
                                                                ('2', 2),
                                                                ('3', 3),
                                                                ('4', 4),
                                                                ('5', 5),
                                                                ('6', 6),
                                                                ('7', 7),
                                                                ('8', 8),
                                                                ('9', 9),
                                                                ('10', 10),
                                                                ('11', 11),
                                                                ('12', 12),
                                                                ('13', 13),
                                                                ('14', 14),
                                                                ('15', 15),
                                                                ('16', 16),
                                                                ('17', 17),
                                                                ('18', 18),
                                                                ('19', 19),
                                                                ('20', 20),
                                                                ('21', 21),
                                                                ('22', 22),
                                                                ('23', 23),
                                                                ('24', 24),
                                                                ('25', 25),
                                                                ('26', 26),
                                                                ('27', 27),
                                                                ('28', 28)
                                                                ]).label("Fecha de Corte"),
                                  fs.is_active.label("Activo")])
            if rend==False:
                return render('account-add.mako',page=page,fs=fs,client_id=client_id)
            else:
                return render('account-add2.mako',page=page,fs=fs,client_id=client_id)

        else:
            #cuenta = session.query(Account).filter(Account.id == client_id).first()
            fs = FieldSet(Account,session=session,data=kwargs)
            fs.configure(include=[fs.name,fs.client_id,fs.address,fs.nit,fs.billing,fs.is_active])
            fs.sync()
            session.commit()
            if return_url is None:
                raise cp.HTTPRedirect('/tables_admin/new/account_data/?client_id=%s'%client_id)
            else:
                raise cp.HTTPRedirect(return_url)
    @cp.expose
    def contract_edit(self,contract_id=None,account_id=None, rend=False, return_url = None, submit = None, **kwargs):
        page=Page("Editar Contrato", '/tables_admin/new/contarct_edit')
        checkpermissions(page.url)
        if contract_id is None and submit:
            contract_id = parseArg(kwargs.keys()[0])[1]
        contract = session.query(Contract).filter(Contract.id == contract_id).first()
        if submit is None:
            fs = FieldSet(contract,session=session)
            fs.configure(include=[fs.start_date,fs.end_date])
            if rend==False: 
                return render('contract-edit.mako',page=page,fs=fs,account_id=account_id)
            else:
                return render('contract-edit2.mako',page=page,fs=fs,account_id=account_id)
        else:
            fs = FieldSet(contract,session=session,data=kwargs)
            fs.configure(include=[fs.start_date,fs.end_date])
            fs.sync()
            session.commit()
            if return_url is None:
                raise cp.HTTPRedirect('/tables_admin/new/contract_data/?account_id=%s'%account_id)
            else:
                raise cp.HTTPRedirect(return_url)
                
    @cp.expose
    def account_edit(self,account_id=None,client_id=None, rend=False, return_url = None, submit = None, **kwargs):
        page=Page("Editar Cuenta", '/tables_admin/new/account_edit')
        checkpermissions(page.url)
        if account_id is None and submit:
            account_id = parseArg(kwargs.keys()[0])[1]
        account = session.query(Account).filter(Account.id == account_id).first()
        if submit is None:
            fs = FieldSet(account,session=session)
            fs.configure(include=[fs.name,fs.address,fs.nit,fs.is_active])
            if rend==False:
                return render('account-edit.mako',page=page,fs=fs,client_id=client_id)
            else:
                return render('account-edit2.mako',page=page,fs=fs,client_id=client_id)
        else:
            fs = FieldSet(account,session=session,data=kwargs)
            fs.configure(exclude=[fs.contracts])
            fs.sync()
            session.commit()
            if return_url is None:
                raise cp.HTTPRedirect('/tables_admin/new/account_data/?client_id=%s'%client_id)
            else:
                raise cp.HTTPRedirect(return_url)
    @cp.expose        
    def client_edit(self,client_id = None, submit = None, rend=False, return_url = None, **kwargs):
        page=Page("Editar Ficha del Cliente", '/tables_admin/new/client_edit')
        checkpermissions(page.url)
        if client_id is None and submit:
            client_id = parseArg(kwargs.keys()[0])[1]
            
        client = session.query(Client).filter(Client.id == client_id).first()
        if submit is None:
            fs = FieldSet(client, session=session)
            fs.configure(include=[fs.name.label("Nombre"),
                                fs.address.label("Direccion"),
                                fs.telephone.label("Telefono"),
                                fs.email,
                                fs.personal_id.label("Cedula"),
                                fs.birthdate.label("Cumpleanhos")]) 
            if rend==False:
                return render('client-add.mako',page=page,fs=fs)
            else:
                return render('client-add2.mako',page=page,fs=fs)
 
        else:
            fs = FieldSet(client,session=session,data=kwargs)
            fs.configure(exclude=[fs.creation_date])
            fs.sync()
            session.commit()
            if return_url is None:
                raise cp.HTTPRedirect('/tables_admin/client_data')    
            else:
                raise cp.HTTPRedirect(return_url)
        
################################################# AQUI ####################################################################
################################################# AQUI ####################################################################

 
 
 
 
 
#----------------------------------------------------------------------------#
#-------------------Paginas-Mantenimient-Sistema-----------------------------#
#----------------------------------------------------------------------------#            
class Page(object):
    """This objects will contain the information needed to render the 
    template and links properly."""
    
    def __init__(self,title, url):

        self.title = title
        self.url = url
        self.menus = getMenus(url)
        self.javascript = ""

    def checkpermissions(self):
        """This proc redirects the user to the root page if the user doesnot have
        permission tu acces the page he requested. """
        if haspermissions(self.url):
            return
        else:
            if logged_in():
                raise cp.HTTPRedirect('/')
            else:
                raise cp.HTTPRedirect('/?return_url=%s' % self.url)
        
def checkpermissions(url):
    """This proc redirects the user to the root page if the user doesnot have
    permission tu acces the page he requested. """
    if haspermissions(url):
        return
    else:
        if logged_in():
            raise cp.HTTPRedirect('/')
        else:
            raise cp.HTTPRedirect('/?return_url=%s' % url)

def logged_in():
    """This proc returns the user that is logged in or None if the user is None
    logged in."""
    user = cp.session.get('logged_as')
    if session and user:
        return user
        print "login"
    else:
        print "no login"
        return None

def haspermissions(url):
    """This procedure checks if the user has 
        permissions to view the page at the url and returns True or false"""

    user = logged_in()

    if session is None or user is None: #if we arent loged in redirect to root
        print "********checkpermissions******** no session or no user"
        return False

    if not (user.role and user.role.actions):
        print "********checkpermissions******** no role or allowedactions"
        return False

    for i in user.role.actions: #if we have permission to be in this page return to the page
#        print "WWWWWWWWW  %s  , %s WWWWWWWWWWWW"%(i.url,url)
        if i.url == url:
            return True
            
    #if we didnt have permission redirec to root page
    print "********checkpermissions******** no permission"
    return False

def getPermitedActions():
    """This funcion return a list of Action objects or None."""
    user = logged_in()
    if user and user.role:
        #print "si user role############################## %s " % user.role.actions
        return user.role.actions   
    else:
        #print "no user role*******/*/*/*/*/*"
        return None

##################Administration pages##################3
class AdminPage():
    """Inside this class the methods that render the list of the objects is defined """
    global session

    @cp.expose
    def index(self):
        page = Page("Pagina de Administracion",'/admin')
        page.checkpermissions()
                
        return render('admin.mako', page=page)    

    @cp.expose
    def module(self,submit=None,**kwargs):
        """List of the other modules thata we will be talking to. """
        
        page = Page('Listado de Modulos','/admin/module')
        page.checkpermissions()
        
        modules = session.query(Module).all()
        if submit is None:
            grid = Grid(Module, modules, session=session)
            grid.configure(include=[grid.name.readonly(),grid.address,grid.ext])
        else:
            grid = Grid(Module,modules, session=session, data=kwargs)
            grid.configure(include=[grid.name.readonly(),grid.address,grid.ext])        
            if grid.validate():
                try:
                    grid.sync()
                    session.commit()
                except Exception , e:
                    session.rollback()
                    return render('error.mako',error="Inesperado %s" % e.args)
        return render('modules-admin.mako', page=page, grid=grid)

    @cp.expose
    def user_role(self):
        """Admin page for user roles, this page only lists all the roles"""

        page = Page("Roles de Usuario","/admin/user_role")
        page.checkpermissions()
        
        roles = session.query(UserRole).all()
        grid = Grid(UserRole, roles)
        show_add_role_link = True
        if haspermissions("/admin/new/user_role"):
            grid.add(Field(name="edit", \
                    value=lambda \
                    item: '<a  href="/admin/new/user_role/?role_id=%s">%s</a>'\
                    % (item.id, 'edit')))
        else:
            show_add_role_link = False
            grid.add(Field(name="edit", value='No'))

        if haspermissions("/admin/delete/user_role"):
            grid.add(Field(name="delete", \
                value=lambda \
                item: '<a  href="/admin/delete/user_role/?role_id=%s">%s</a>'\
                % (item.id, 'delete')))
        else:
            grid.add(Field(name="delete", value='No'))
            
        grid.configure(include = [
            grid.name.readonly(),
            grid.actions,
            grid.edit.readonly(),
            grid.delete.readonly()
        ])
        return render('admin-user-roles.mako', page=page, grid=grid, show_add_link = show_add_role_link)
            
    @cp.expose
    def action(self):
        """Admin actions """
        page = Page('Admin. Acciones','/admin/action')
        page.checkpermissions()
        actions = session.query(Action)
        grid = Grid(Action, actions,session=session)
        add_link_name = "Nueva Accion"
        if haspermissions('/admin/new/action'):
            grid.add(Field(name="edit", \
                    value=lambda \
                    item: '<a  href="/admin/new/action/?action_id=%s">%s</a>'\
                    % (item.id, 'edit')))
        else:   
            add_link_name = ""
            grid.add(Field(name="edit", value="No"))
        if haspermissions('/admin/delete/action'):
            grid.add(Field(name="delete", \
                value=lambda \
                item: '<a  href="/admin/delete/action/?action_id=%s">%s</a>'\
                % (item.id, 'delete')))
        else:
            grid.add(Field(name="delete", value="No"))
        grid.configure(include = [
            grid.name.readonly(),
            grid.url.readonly(),
            grid.edit.readonly(),
            grid.delete.readonly()
        ])

        return render('admin-grid.mako',page=page, grid = grid,
            add_link = "/admin/new/action", add_link_name = add_link_name)

        

    @cp.expose
    def user(self):
        """List of all the existing users, actions that are linked from here are: new, edit, delete user
           """
        page = Page("Admin. Usuarios","/admin/user")
        page.checkpermissions()
        
        if session:          
            users = session.query(User).order_by(User.name).all()
            grid = Grid(User, session.query(User))
            add_link_name = "Crear Usuario"
            if haspermissions('/admin/delete/user'):
                grid.add(Field(name="delete", \
                    value=lambda \
                    item: '<a  href="/admin/delete/user/?username=%s">%s</a>'\
                    % (item.name, 'delete')))
            else:
                grid.add(Field(name='delete', value="No"))

            if haspermissions('/admin/new/user'):
                grid.add(Field(name="edit",\
                    value=lambda\
                    item: '<a  href="/admin/new/user?username=%s">%s</a>'\
                    % (item.name, 'edit')))
            else:
                grid.add(Field(name='edit', value='No').label(''))
                add_link_name = ""
                
            grid.configure(include=[grid.name.readonly(),grid.role.readonly(),grid.is_active.readonly(), grid.edit.readonly(), grid.delete.readonly()])
            return render('admin-users.mako', page=page, grid = grid, add_link_name=add_link_name)
        else:
            raise cp.HTTPRedirect('/')

def parseArg(s):
    """this proc makes a reg exp from an arg in the form Table-id-attribute
    and returns a list in the form [TableName, id, attribute]"""
    
    
    return s.split('-')

def required_esp(value, field):
     if value=="":
         raise validators.ValidationError('Este campo es obligatorio.')

class AddEditObjectPage():
    """This class contins methods for adding-editing new objects """
    @cp.expose
    def index(self):
        page = Page('Add or Edit an object','/admin/add')
        page.checkpermissions()
        return render('error.mako',page=page,msg="""
            Lo sentimos.
            Link invalido.
        """)

    @cp.expose
    def action(self,action_id=None,submit=None,**kwargs):
        
        page = Page('Accion','/admin/new/action')
        page.checkpermissions()
        if submit: #data manipulation
            if submit == "Editar":
                pk = parseArg(kwargs.keys()[0])[1]
                action = session.query(Action).filter(Action.id == pk).first()
                if action is None:
                    return render('error.mako',page=page,
                        error="""Esa Accion no existe. """)
                
                if session.query(UserRole).filter(UserRole.actions.contains(action)).first():
                    return render('error.mako', page=page,
                        error=""" Esa accion no puede ser editada ya que es
                         necesaria para garantizar el funcionamiento 
                         adecuado del sistema""")
                
                fs = FieldSet(action,session=session,data=kwargs)
                
            else:
                fs = FieldSet(Action,session = session, data=kwargs)
            fs.configure(include=[
                    fs.name.validate(required_esp),
                    fs.url
                ])

            if fs.validate() == False:
                page.title = "Por Favor corrija los datos ingresados."
                return render('admin-add-edit.mako',
                    page=page,
                    fs=fs,
                    action='',
                    submit_value = submit)
            
            
            fs.sync()
            try:
                session.commit()
            except Exception , e:
                session.rollback()
                return render('error.mako',page=page, error='Error: %s' % e.args)
            raise cp.HTTPRedirect("/admin/action")
        else: #form generation
            if action_id is None: #new action
                fs = FieldSet(Action,session = session)
                fs.configure(include=[
                    fs.name.required(),
                    fs.url.required()
                ])
                page.title = 'Crear Accion'
                return render('admin-add-edit.mako',
                    page=page,
                    fs=fs,
                    action='', submit_value = "Crear")
            else: #edit action
                action = session.query(Action).filter(Action.id==action_id).first()
                if action is None:
                    return render('error.mako', page=page,error='Esta accion no existe.')
            
                if session.query(UserRole).filter(UserRole.actions.contains(action)).first():
                    return render('error.mako', page=page,
                        error=""" Esa accion no puede ser editada ya que es
                         necesaria para garantizar el funcionamiento 
                         adecuado del sistema""")
        
                fs = FieldSet(action)
                fs.configure(include=[
                    fs.name.required(),
                    fs.url.required()
                ])
                page.title = 'Editar Accion'
                return render('admin-add-edit.mako',
                    page=page,
                    fs=fs,
                    action='',
                    submit_value = "Editar")



    @cp.expose
    def user_role(self,submit=None,role_id=None, **kwargs):

        page = Page('Agregar Rol','/admin/new/user_role')
        page.checkpermissions()

        if submit: #data manipulation
            if submit == "Update":
                pk = parseArg(kwargs.keys()[0])[1]
                user_role = session.query(UserRole).filter(UserRole.id == pk).one()
                
                if user_role.name == "Administrador":
                    return render('error.mako', page=page,
                        error="""No se permite editar este rol ya que este 
                            viene por defecto en el sistema y es critico para garantizar
                            su funcionamiento adecuado.""")
                
                ##all the user with the modifiing role must have db permission refreshed
                try:
                    for u in session.query(User).filter(User.role==user_role).all():
                        u.refreshDBPermitions(root_session)
                    root_session.commit()
                except:
                    root_session.rollback()
                    print "ERROR: en refreshDBPermition"
                ##
                
                
                fs = FieldSet(user_role,session=session,data=kwargs)
                
            else:
                fs = FieldSet(UserRole,session = session, data=kwargs)
            fs.configure(include=[
                    fs.name,
                    fs.actions.checkbox()
                ])
            
            if fs.validate() == False:
                return render('admin-add-edit.mako',
                    page=page,
                    fs=fs,
                    title="Agregar o Editar Rol",
                    action='', submit_value = submit)
                
            try:
                fs.sync()
                session.commit()
            except Exception , e:
                session.rollback()
                return render('error.mako',page=page, error='Error: %s' % e.args)
            raise cp.HTTPRedirect("/admin/user_role")
        else: #form generation
            if role_id is None: #new role
                fs = FieldSet(UserRole,session = session)
                fs.add(Field(name="go_to", value=lambda  item: '<a  href="/admin/action/">Manage Actions</a>'))
                fs.configure(include=[
                    fs.go_to.readonly(),
                    fs.name,
                    fs.actions.checkbox()
                ])
                return render('admin-add-edit.mako',
                    page=page,
                    fs=fs,
                    title="Agregar o Editar Rol",
                    action='', submit_value = "New")
            else: #edit role
                role = session.query(UserRole).filter(UserRole.id==role_id).first()
                if role is None:
                    return render('error.mako', page=page,error='Ese rol no existe en la base de datos')
        
                if role.name == "Administrador":
                    return render('error.mako', page=page,
                        error="""No se permite editar este rol ya que este 
                            viene por defecto en el sistema y es critico para garantizar
                            su funcionamiento adecuado.""")
        
                fs = FieldSet(role)
                fs.add(Field(name="go_to", value=lambda  item: '<a  href="/admin/action/">Manage Actions</a>'))
                fs.configure(include=[
                    fs.go_to.readonly(),
                    fs.name,
                    fs.actions.checkbox()
                ])
                return render('admin-add-edit.mako',
                    page=page,
                    fs=fs,
                    action='',
                    submit_value = "Update")



    @cp.expose
    def user(self,username=None,new_user_name=None, pwd0=None, pwd1=None,submit=None,**kwargs):
        """Sirve para insertar un usuario nuevo en la base de datos o editar un usuario existente

        Para un usuario nuevo debo revisar si el usuario no existe antes de crearlo
"""

        page = Page('Agregar Usuario','/admin/new/user')
        page.checkpermissions()
        
        if submit: #data manipulation
            if new_user_name: #code for inserting the new user
                if len(new_user_name)==0:
                    return render('error.mako', page=page,error="Por favor ingrese un nombre de usuario.")
                if len(pwd0)==0:
                    return render('error.mako', page=page,error="Por favor ingrese una contrasenia.")
                if pwd0==pwd1:
                    try:
                        u = session.query(User).filter(User.name==new_user_name).first()

                        if u and u.name == new_user_name:
                            return render('error.mako', page=page, error='Ese usuario ya existe en la base de datos')
                        user = User()
                        user.name=new_user_name
                        user.pwd=pwd0
                        session.add(user)
                        try:
                            user.makeDBUser(root_session)
                        except Exception , e:
                            session.rollback()
                            root_session.rollback()
                            return render('error.mako', page=page,error="Ese no nombre no esta disponible por favor escoja otro. %s" % e.args)
                        user.refreshDBPermitions(root_session)
                        root_session.commit()
                        session.commit()
                    except Exception , e:
                        session.rollback()
                        root_session.rollback()
                        return render('error.mako', page=page,error="%s" % e.args)
                    raise cp.HTTPRedirect('/admin/user')
                else:
                    return render('error.mako',page=page,error="Fallo la verificacion de password")
            
            else: #code for editin the existing user
                user = session.query(User).filter(User.name==username).first()
                if user is None:
                    return render('error.mako',page=page,error="Ese usuario no existe.")
                    
                if user.name == 'root':
                    return render('error.mako', page=page,
                    error="""No se permite editar este usuario ya que este 
                        viene por defecto en el sistema y es critico para
                        garantizar su funcionamiento adecuado.""")
                try:
                    fs = FieldSet(user, session = session, data=kwargs)
                    fs.configure(include=[
                            fs.name.readonly(),
                            fs.pwd,
                            fs.is_active,
                            fs.role
                        ])
                    fs.sync()
                    user.refreshDBPermitions(root_session)
                    root_session.commit()
                    session.commit()
                except Exception , e:
                    session.rollback()
                    return render('error.mako',page=page,error="Error inesperado: %s" % e.args)
                raise cp.HTTPRedirect('/admin/user')
        else: #form generation
            if username: #code for form generation(edit user)
                user = session.query(User).filter(User.name == username).first()
                
                if user is None:
                    return render('error.mako', page=page,
                    error="""Ese usuario no existe en la base de datos.""")
                
                if user.name == 'root':
                    return render('error.mako', page=page,
                    error="""No se permite editar este usuario ya que este 
                        viene por defecto en el sistema y es critico para
                        garantizar su funcionamiento adecuado.""")
                
                fs = FieldSet(user,session=session)
                fs.configure(include=[
                        fs.name.readonly(),
                        fs.pwd,
                        fs.is_active,
                        fs.role
                    ])
                return render('add-edit-user.mako',page=page,fs=fs,action="")
            else: #code for form generation (new user)
                return render('add-edit-user.mako',page=page,fs=None,action="")


class DeleteObjectPage():
    """This class contins methods for deleting new objects """
    @cp.expose
    def index(self):
        return "delete object"

    @cp.expose
    def user(self,username):
        page = Page('Delete User','/admin/delete/user')
        page.checkpermissions()
        
        u = session.query(User).filter(User.name==username).first()
        if u:
        
            if u.name == "root":
                return render('error.mako', page=page,
                    error="""No se permite borrar este rol, este 
                        viene por defecto en el sistema y es critico para
                        garantizar su funcionamiento adecuado.""")
            if u.name == cp.session.get('logged_as').name:
                return render('error.mako', page=page, error='No se permite borrar tu propio usuario, hable con otro administrador para que realice esta accion.')
            try:
                session.delete(u)
                session.commit()
                s = text('DROP ROLE crm_%s' % (username))
                root_session.execute(s)
                root_session.commit()
            except:
                return render('error.mako',page=page, error='No se pudo borrar el ususuario porque existen dependencias con el, si ya no lo desea usar lo puede desactivar en la pagina de editar usuario.')
            raise cp.HTTPRedirect('/admin/user')
        else:
            return render('error.mako',page=page,error='No se pudo borrar el ususuario porque no existe en la base de datos.')

    @cp.expose
    def user_role(self,role_id):
        """Delets a user_role if it doesnot have dependencies."""
        page = Page("Borrar Rol","/admin/delete/user_role")
        checkpermissions(page.url)
        u = session.query(UserRole).filter(UserRole.id == role_id).first()
        if u:

            if u.name == "Administrador":
                return render('error.mako', page=page,
                    error="""No se permite borrar este rol ya que este 
                        viene por defecto en el sistema y es critico para garantizar
                        su funcionamiento adecuado.""")

            if session.query(User).filter(User.role==u).first() is None:
                try:
                    session.delete(u)
                    session.commit()
                except Exception , e:
                    session.rollback()
                    return render('error.mako',page=page,error='Error Insesperado en la base de datos. %s'%e.args)
                raise cp.HTTPRedirect('/admin/user_role')
            else:
                return render('error.mako',page=page,error='No se pudo borrar el Rol, Primero cambie de rol a los usuarios existentes con este rol.')
        else:
            return render('error.mako',page=page,error='No se pudo borrar el rol porque no existe en la base de datos.')

    @cp.expose
    def action(self,action_id):
        """Deletes a action if it doesnot have dependencies, 
        the actions that are created by default should not be able to 
        be deleted, because they conform the Admin role, that should not be able
        to be edited nor deleted either."""
        
        page = Page("Borrar Accion", '/admin/delete/action')
        checkpermissions(page.url)
        a = session.query(Action).filter(Action.id==action_id).first()
        if a:
            if session.query(UserRole).filter(UserRole.actions.contains(a)).first() is None:
                try:
                    session.delete(a)
                    session.commit()
                except Exception , e:
                    session.rollback()
                    return render('error.mako',page=page,error='Error Insesperado en la base de datos. %s'%e.args)
                raise cp.HTTPRedirect('/admin/action')
            else:
                return render('error.mako',page=page,
                    error="""No se pudo borrar la accion debido a que existen 
                            roles que usan esta accion, si esta es una accion
                            que viene por defecto entonces no se puede borrar
                            ni editar.""")
        else:
            return render('error.mako',page=page,error='No Existe esa accion en la base de datos')
############################end of admon pages@@@@@@@@@2####
class SessionPage():
    @cp.expose
    def login(self,username,pwd,return_url=None,**kw):
        """Verificates the password and username entered and 
           Creates a connection with the db using username"""
        global session

        try: 
            user = root_session.query(User).filter(User.name == username).filter(User.pwd == pwd).filter(User.is_active == True).one()
            cp.session['logged_as'] = user
            session = DataBase("crm_"+user.name, user.pwd).Session()
        except:
            cp.session['logged_as'] = None
            session = None
            #return root.index(True)
            #render('login.mako', login_error=True)
            raise cp.HTTPRedirect('/')


        if return_url:
            raise cp.HTTPRedirect(return_url)
        else:
            raise cp.HTTPRedirect('/')

    @cp.expose
    def logout(self):
        """Close the connection with the database and logs out the user"""
        global session
        try:
            session.close()
        except:
            pass
        cp.session['logged_as'] = None
        raise cp.HTTPRedirect('/')
        

def session_render():
    "Returns html code for loguin in or out depending."
    logged_as = cp.session.get('logged_as')
    if logged_as:
        return "Logueado como: "+logged_as.name+" <a href=/session/logout>Cerrar Session</a>"
    else:
        return render("login.mako")


def getMenus(current_url):
    """This procedure returns a tuple conaining 
    (the links of the menu,links of the submenu).
    the links are a dictionary in the form:
    {link_name:link_url}"""
    actions = getPermitedActions()
    menu = {}
    submenu = {}
    if actions:
        for i in actions:
            scurl = current_url.split("/")
            slink = i.url.split("/") #admin, user, new
           # print "DEBUG:###################### curls=%s slink= %s" % (scurl,slink)
            if len(slink) == 2:
                menu[i.name] = i.url
            elif len(slink) == 3:
                if scurl[1] == slink[1]:
                    submenu[i.name] = i.url
    else:
        submenu['Home']="/"
        menu['Home']="/"

    return (menu,submenu)
    

class RootPage():
    @cp.expose
    def index(self,return_url=None, login_error=False):
        ###TODO: remove autologin
        global session
        #cp.session['logged_as'] = root_session.query(User).filter(User.name == 'root').first()
        #session = DataBase("crm_root", 'root').Session()
        ##
        page = Page("Home","/")
        page.menus = getMenus(page.url)
        logged_as = cp.session.get('logged_as')
        if logged_as:
            if return_url:
                raise cp.HTTPRedirect(return_url)
            else:
                return render("login.mako",page=page, logged=True)
        else:
            return render("login.mako",page=page, loged=False, 
                login_error=login_error, return_url = return_url)

                
#----------------------------------------------------------------------------#
#-----------------Inicializacion-de-datos-default-en-DB----------------------#
#----------------------------------------------------------------------------#
def data_init():
    """This procedure will be called at the server startup and its 
        porpouse is to initializate data on the database."""

###init actions
    if root_session.query(Action).first() is None:


        init_list = [
        
            ("Home","/"),
            ("Atencion al Cliente","/serve_client"),

                ('Buscar Cliente','/serve_client/search'),
                ('Principal Atender','/serve_client/main'),

            ('Administracion Principal','/tables_admin'),
                ('Ficha del Cliente','/tables_admin/client_data'), 

            ('Administracion Principal','/tables_admin'),
                ('Ficha del Cliente','/tables_admin/client_data'), 
                ('Cuentas','/tables_admin/account_main'),
                ('Categoria del cliente','/tables_admin/category'),
                    ("Editar Ficha del Cliente", '/tables_admin/new/client_edit'),
                    ("Editar Cuenta", '/tables_admin/new/account_edit'),
                    ("Editar Contrato", '/tables_admin/new/contarct_edit'),
                    ('Agregar Ficha del Cliente','/tables_admin/new/client_data'),
                    ('Agregar Ficha de la Empresa','/tables_admin/new/company'),
                    ('Listado de Cuentas','/tables_admin/new/account_data'),
                    ("Listado de Contratos","/tables_admin/new/contract_data"),
                    ('Listado de Cuentas','/tables_admin/new/account'),
                    ("NuevoContrato", '/tables_admin/new/contract'),
                    ('Nueva Categoria','/tables_admin/new/add_category'),

                
                
            ("Administracion del Sistema","/admin"),					   
                ("Admin. Usuario","/admin/user"),
                    ("Nuevo Usuario","/admin/new/user"),
                    ("Editar Usuario","/admin/new/user"),
                    ("Borrar Usuario","/admin/delete/user"),
                ("Admin. Roles","/admin/user_role"),
                    ("Nuevo Rol","/admin/new/user_role"),
                    ("Editar Rol","/admin/new/user_role"),
                    ("Borrar Rol","/admin/delete/user_role"),
                ("Admin Acciones","/admin/action"),
                    ("Nueva Accion","/admin/new/action"),
                    ("Editar Accion","/admin/new/action"),
                    ("Borrar Rol","/admin/delete/action"),					
                ("Admin Modulos","/admin/module"),
                    ("Editar Modulo", "/admin/new/module")
       ]


        for name,url in init_list:
            a = Action()
            a.name = name
            a.url = url
            root_session.add(a)
        root_session.commit()

##create admin role and root user
    print "LOG: create admin role and root user"
    if root_session.query(UserRole).first() is None:
        role = UserRole()
        role.name = "Administrador"
        role.actions = root_session.query(Action).all()
        root_session.add(role)
        root_session.commit()

    if root_session.query(User).filter(User.name=='root').first() is None:
        ru = User()
        ru.role = role
        ru.name = 'root'
        ru.pwd = 'root'
        ru.is_active = True
        root_session.add(ru)
        root_session.commit()
        try:
            ru.makeDBUser(root_session)
        except:
            root_session.rollback()
    
        ru.refreshDBPermitions(root_session)
        root_session.commit()
        
## insert the name of the modules:

    if root_session.query(Module).first() is None:
        modules_list = ['Inventario','Facturacion','Incidencias','Colas']
        for i in modules_list:
            m = Module()
            m.name = i
            m.address = "sin-configurar"
            m.ext = ""
            root_session.add(m)
            root_session.commit()


    sequence_list = ["accounts_id_seq",
    "user_id_seq",
    "client_classification_catalog_id_seq",
    "clients_id_seq",
    "contacts_id_seq",
    "client_category_id_seq",
    "client_category_catalog_id_seq",
    "contracts_id_seq",
    "lines_id_seq",
    "user_roles_id_seq",
    "actions_id_seq"]
    
    for i in sequence_list:
        s = text("GRANT UPDATE ON %s TO PUBLIC" % (i))
        root_session.execute(s)  
        print "permission to public on %s" % i

    root_session.commit()

if __name__ == "__main__":
    
    root_session = DataBase('crmroot','crmpwd').Session()
    root = RootPage()
    root.admin = AdminPage()
    root.admin.new = AddEditObjectPage()
    root.admin.delete = DeleteObjectPage()
    root.session = SessionPage()
    root.tables_admin = TablesAdminPage()
    root.tables_admin.new = TableObjectAddEdit()
    root.serve_client = ServeClientPage()
    root.info = ServeXMLClientData()
    root.info.clientes = ServeXMLClientData()
    data_init()

    cp.quickstart(root, config='app.conf')

    
    
